import os
from posixpath import dirname
from xlrd import open_workbook
from xml.etree import ElementTree as ElementTree
import sys
dir_name=os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
print(dirname)
sys.path.insert(0, '../common')
sys.path.insert(0,dir_name)
from log import MyLog as Log
from configHttp import ConfigHttp
import readConfig
prDir=readConfig.pro_dir
localCofigHttp=ConfigHttp()
log=Log.get_log()
logger = log.get_logger()
def get_xls(xls_name,sheet_name):
    cls =[]
    xlsPath=os.path.join(prDir,"testfile",xls_name)
    file =open_workbook(xlsPath)
    print(dir(file))
    sheet =file.sheet_by_name(sheet_name)
    nrows=sheet.nrows
    print(nrows)
    for i in range(nrows):
        if sheet.row_values(i)[0] !=u'case_name':
            cls.append(sheet.row_values(i))
    return cls
database={}
def set_xml():
    if len(database)==0:
        sql_path=os.path.join(prDir,"testfile","SQL.xml")
        tree =ElementTree.parse(sql_path)
        for db in tree.findall("database"):
            db_name =db.get("name")
            table ={}
            for tb in db.getchildren():
                table_name=tb.get("name")
                sql={}
                for data in tb.getchildren():
                    sql_id =data.get("id")
                    sql[sql_id]=data.text
                table[table_name]=sql
            database[db_name]=table
def get_xml_dict(database_name,table_name):
    set_xml()
    database_dict=database.get(database_name).get(table_name)
    return database_dict
def get_sql(database_name,table_name,sql_id):
    db=get_xml_dict(database_name,table_name)
    sql=db.get(sql_id).strip()
    return sql
if __name__ =="__main__":
    res =get_xls("base.xlsx","Sheet1")
    print(res)
    res1 = get_sql("student","student","select data")
    print(res1)
